package com.bms.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.bms.entity.Book;

public class BookDao {

	/**
	 * 保存图书的信息：1、将数据保存到books表 2、将和作者的关系表中插入数据 ---->手动控制事务的提交
	 * 
	 * @param book
	 *            需要保存的图书的信息
	 */
	public void insert(Book book) {
		String url = "jdbc:mysql://localhost:3306/books";// 数据库地址
		String user = "fangzuchang";// 数据库登录名
		String password = "13920778329";
		Connection con = null;// 数据库链接对象
		PreparedStatement statement = null;
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url, user, password);
			// 设置事务是否为自动提交--->false(默认值为true)
			con.setAutoCommit(false);
			// 完成图书的添加
			// 1、图书本身的信息添加到图书表
			String sql = "insert into books(ISBN,name,price,version,publishTime,publisherId) values(?,?,?,?,?,?)";
			statement = con.prepareStatement(sql);
			// 绑定数据
			statement.setString(1, book.getIsbn());
			statement.setString(2, book.getName());
			statement.setFloat(3, book.getPrice());
			statement.setInt(4, book.getVersion());
			statement.setDate(5, book.getPublishTime());
			statement.setInt(6, book.getPublisherId());
			statement.execute();
			// 链接泄漏问题--->
			statement.close();
			// 2、添加数据到author_book(中间表)--->维系关系
			int[] authorsId = book.getAuthorsId();
			String sql2 = "insert into author_book(isbn,authorId) values(?,?)";
			statement = con.prepareStatement(sql2);
			// 批量添加数据
			for (int i = 0; i < authorsId.length; i++) {
				statement.setString(1, book.getIsbn());
				statement.setInt(2, authorsId[i]);
				// 批量管理sql
				statement.addBatch();
			}
			// 执行语句
			statement.executeBatch();

			// 提交事务--->将数据同步到数据库中
			con.commit();
		} catch (Exception e) {
			try {
				// 回滚事务
				con.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public void update(Book book) {
		String url = "jdbc:mysql://localhost:3306/books";// 数据库地址
		String user = "fangzuchang";// 数据库登录名
		String password = "13920778329";
		Connection con = null;// 数据库链接对象
		PreparedStatement statement = null;
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url, user, password);
			// 设置事务是否为自动提交--->false(默认值为true)
			con.setAutoCommit(false);
			// 完成图书的添加
			// 1、图书本身的信息修改到图书表
			String sql = "update books set name=?,price=?,version=?,publishTime=?,publisherId=? where ISBN=?";
			statement = con.prepareStatement(sql);
			// 绑定数据
			statement.setString(1, book.getName());
			statement.setFloat(2, book.getPrice());
			statement.setInt(3, book.getVersion());
			statement.setDate(4, book.getPublishTime());
			statement.setInt(5, book.getPublisherId());
			statement.setString(6, book.getIsbn());
			statement.execute();
			// 链接泄漏问题--->
			statement.close();

			// 2、通过ISBN先删除中间表的数据
			String sql1 = "delete from author_book where isbn=?";
			statement = con.prepareStatement(sql1);
			statement.setString(1, book.getIsbn());
			statement.execute();
			statement.close();

			// 3、添加数据到author_book(中间表)--->维系关系
			int[] authorsId = book.getAuthorsId();
			String sql2 = "insert into author_book(isbn,authorId) values(?,?)";
			statement = con.prepareStatement(sql2);
			// 批量添加数据
			for (int i = 0; authorsId != null && i < authorsId.length; i++) {
				statement.setString(1, book.getIsbn());
				statement.setInt(2, authorsId[i]);
				// 批量管理sql
				statement.addBatch();
			}
			// 执行语句
			statement.executeBatch();

			// 提交事务--->将数据同步到数据库中
			con.commit();
		} catch (Exception e) {
			try {
				// 回滚事务
				con.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 检测ISBN是否存在
	 * 
	 * @param isbn
	 * @return true 表示ISBN存在
	 */
	public boolean checkIsbn(String isbn) {
		String url = "jdbc:mysql://localhost:3306/books";// 数据库地址
		String user = "fangzuchang";// 数据库登录名
		String password = "13920778329";
		Connection con = null;// 数据库链接对象
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url, user, password);
			String sql = "select * from books where ISBN=?";
			statement = con.prepareStatement(sql);
			// 绑定数据
			statement.setString(1, isbn);
			resultSet = statement.executeQuery();
			// 查询数据库--->是否具有ISBN的数据--->resultSet是否有数据
			if (resultSet.next()) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return false;
	}

	/**
	 * 根据ISBN删除图书的信息
	 * 
	 * @param isbn
	 */
	public void delete(String isbn) {
		String url = "jdbc:mysql://localhost:3306/books";// 数据库地址
		String user = "fangzuchang";// 数据库登录名
		String password = "13920778329";
		Connection con = null;// 数据库链接对象
		PreparedStatement statement = null;
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url, user, password);
			con.setAutoCommit(false);
			// 1、先根据ISBN删除中间表的数据---->isbn(外键---->books(id))
			String sql = "delete from author_book where isbn=?";
			statement = con.prepareStatement(sql);
			// 绑定数据
			statement.setString(1, isbn);
			statement.execute();
			statement.close();
			// 2、再删除图书自身表中的数据
			String sql2 = "delete from books where ISBN=?";
			statement = con.prepareStatement(sql2);
			// 绑定数据
			statement.setString(1, isbn);
			statement.execute();

			con.commit();
		} catch (Exception e) {
			try {
				con.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 查询所有的图书
	 * 
	 * @return
	 */
	public List<Book> queryAll() {
		String url = "jdbc:mysql://localhost:3306/books";// 数据库地址
		String user = "fangzuchang";// 数据库登录名
		String password = "13920778329";
		Connection con = null;// 数据库链接对象
		Statement statement = null;
		ResultSet resultSet = null;

		List<Book> books = new ArrayList<Book>();
		Book book = null;

		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url, user, password);
			statement = con.createStatement();
			String sql = "select b.*,p.`name` publisherName from books b,publishers p where b.publisherId=p.id";
			resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				String isbn = resultSet.getString("ISBN");
				String name = resultSet.getString("name");
				float price = resultSet.getFloat("price");
				int version = resultSet.getInt("version");
				Date publishTime = resultSet.getDate("publishTime");
				String publisherName = resultSet.getString("publisherName");

				book = new Book();
				book.setIsbn(isbn);
				book.setName(name);
				book.setPrice(price);
				book.setVersion(version);
				book.setPublishTime(publishTime);
				book.setPublisherName(publisherName);

				books.add(book);
				book = null;
			}
			return books;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	/**
	 * 
	 * @param param
	 *            String---->key--->查询的条件 Object---->value->查询条件的值
	 * @return
	 */
	public List<Book> queryAll(Map<String, Object> param) {
		String url = "jdbc:mysql://localhost:3306/books";// 数据库地址
		String user = "fangzuchang";// 数据库登录名
		String password = "13920778329";
		Connection con = null;// 数据库链接对象
		Statement statement = null;
		ResultSet resultSet = null;

		List<Book> books = new ArrayList<Book>();
		Book book = null;

		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url, user, password);
			statement = con.createStatement();
			String sql = "select b.*,p.`name` publisherName from books b,publishers p where b.publisherId=p.id";

			// 获取查询条件
			Set<String> keySet = param.keySet();
			if (keySet != null && !keySet.isEmpty()) {
				// 遍历所有的查询条件
				for (String key : keySet) {
					sql = sql + " and " + key + " = '" + param.get(key) + "'";
				}
			}

			resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				String isbn = resultSet.getString("ISBN");
				String name = resultSet.getString("name");
				float price = resultSet.getFloat("price");
				int version = resultSet.getInt("version");
				Date publishTime = resultSet.getDate("publishTime");
				String publisherName = resultSet.getString("publisherName");

				book = new Book();
				book.setIsbn(isbn);
				book.setName(name);
				book.setPrice(price);
				book.setVersion(version);
				book.setPublishTime(publishTime);
				book.setPublisherName(publisherName);

				books.add(book);
				book = null;
			}
			return books;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	/**
	 * 获取图书的信息
	 * 
	 * @param param
	 * @return
	 */
	public Book getBookInfo(String param) {
		String url = "jdbc:mysql://localhost:3306/books";// 数据库地址
		String user = "fangzuchang";// 数据库登录名
		String password = "13920778329";
		Connection con = null;// 数据库链接对象
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		Book book = null;

		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url, user, password);
			String sql = "select b.*,p.`name` publisherName from "
					+ "books b,publishers p where b.publisherId=p.id and b.ISBN=?";
			statement = con.prepareStatement(sql);

			statement.setString(1, param);

			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				String isbn = resultSet.getString("ISBN");
				String name = resultSet.getString("name");
				float price = resultSet.getFloat("price");
				int version = resultSet.getInt("version");
				Date publishTime = resultSet.getDate("publishTime");
				int publisherId = resultSet.getInt("publisherId");
				String publisherName = resultSet.getString("publisherName");

				book = new Book();
				book.setIsbn(isbn);
				book.setName(name);
				book.setPrice(price);
				book.setVersion(version);
				book.setPublishTime(publishTime);
				book.setPublisherId(publisherId);
				book.setPublisherName(publisherName);
			}
			return book;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return null;
	}

}
